package com.shopping.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.shopping.modean.User;
import com.shopping.util.DBHelper;

public class UserDAO {
	
	/**
	 * 保存用户
	 * 
	 * @date 2018-05-29
	 */
	public void  insert(User user) {
		String sql = "insert into ruser values (null,?,?,?,?,now())";
		Connection conn = DBHelper.getConn();
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setString(1,user.getUsername());
			pstmt.setString(2,user.getPassword());
			pstmt.setString(3,user.getPhone());
			pstmt.setString(4,user.getAddr());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
	}

	/**
	 * 查询所有的用户
	 * 
	 * @date 2018-06-01
	 */
	public List<User> getUsers() {
		List<User> users = new ArrayList<User>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from ruser";
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPhone(rs.getString("phone"));
				u.setAddr(rs.getString("addr"));
				u.setRdate(new Date(rs.getTimestamp("rdate").getTime()));
				users.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return users;
	}

	/**
	 * 查询用户
	 * 
	 * @date 2018-06-02
	 */
	public void delete(int id) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DBHelper.getConn();
			stmt = DBHelper.getStmt(conn);
			String sql = "delete from ruser where id = " + id ;
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(stmt);
			DBHelper.closeConn(conn);
		}
		
	}

	/**
	 * 根据用户名查询用户
	 * 
	 * @date   2018-06-02
	 * @return 可能返回null
	 */
	public User findByUsername(String username) {
		User user = null;;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from ruser where username = '" + username + "'";
			System.out.println("sql:" + sql);
			rs = DBHelper.executeQuery(conn, sql);
			if(rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setPassword(rs.getString("password"));
				user.setUsername(username);
				user.setPhone(rs.getString("phone"));
				user.setAddr(rs.getString("addr"));
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return user;
	}

	/**
	 * 修改用户
	 * 
	 * @date 2018-06-02
	 */
	public void update(User user) {
		String sql = "update ruser set username =?, phone =?, addr =? where id = " + user.getId();
		Connection conn = DBHelper.getConn();
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setString(1,user.getUsername());
			pstmt.setString(2,user.getPhone());
			pstmt.setString(3,user.getAddr());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
		
	}
}
